import datetime
import json

import pandas as pd

import sql_util
from work_zykj.mysql import mysql_util

if __name__ == '__main__':
    file_path = 'C:/Users/yangjianzhang/Desktop/销量大于4吨.xlsx'
    # 使用read_excel函数读取Excel文件并将其存储为DataFrame对象
    df = pd.read_excel(file_path, converters={'company_code': str})
    # 打印读取到的数据
    # print(df)
    print(type(df))
    province_list = []
    company_code_list = []
    cv_code_list = []
    cv_type_list = []
    sale_qty_list = []
    farm_list = []
    farm_type_list = []
    capacity_list = []
    capacity_crm_list = []
    capacity_piglet_crm_list = []
    capacity_piglet_list = []
    for index, row in df.iterrows():
        # print(row['push_content'])
        province = row['province']
        company_code = row['company_code']
        cv_code = row['cv_code']
        cv_type = row['cv_type']
        sale_qty = row['sale_qty']
        farm = ''
        farm_type = ''
        capacity = ''
        capacity_crm = ''
        capacity_piglet = ''
        capacity_piglet_crm = ''

        # print('%s-%s' % (org_code, order_id))
        sql = "select distinct org_code,farm_org,farm_type,breed_scale,fat_scale from ( \
        select t.org_code,t.farm_org,'1' as farm_type,0 fat_scale,0 breed_scale from pc_ss_relation t where t.cv_code = '{}' \
        union all \
        select farm_id as org_code,farm_id as farm_org,'0' as farm_type,i.fat_scale,i.breed_scale from bae_farm_information i where i.cus_code = '{}' \
        ) a ".format(cv_code, cv_code)
        rs = mysql_util.select_by_sql(sql)
        rs = json.loads(rs)
        if len(rs) > 0:
            for f in rs:
                org_code = f['org_code']
                farm_org = f['farm_org']
                farm_type = f['farm_type']
                if '1' == farm_type:
                    farm = org_code + "#" + farm_org
                    farm_type = '专业版'
                    try:
                        farm_org_split = farm_org.split('-')[0]
                        # 查询专业版规模
                        sql = "select t.capacity,t.capacity_piglet from fr_mas_farm_information t where t.org_code = '{}' " \
                              "and farm = '{}'".format(org_code, farm_org_split)
                        rs = sql_util.select_by_org(org_code, sql)
                        rs = json.loads(rs)
                        if len(rs) > 0:
                            capacity = rs[0]['CAPACITY']
                            capacity_piglet = rs[0]['CAPACITY_PIGLET']
                    except Exception as e:
                        print(e)

                else:
                    farm = org_code
                    farm_type = '家庭版'
                    # 查询家庭版规模
                    capacity = f['breed_scale']
                    capacity_piglet = f['fat_scale']

                province_list.append(province)
                company_code_list.append(company_code)
                cv_code_list.append(cv_code)
                cv_type_list.append(cv_type)
                sale_qty_list.append(sale_qty)
                farm_list.append(farm)
                farm_type_list.append(farm_type)
                capacity_list.append(capacity)
                capacity_crm_list.append(capacity_crm)
                capacity_piglet_list.append(capacity_piglet)
                capacity_piglet_crm_list.append(capacity_piglet_crm)
        else:
            # 没有场的数据也要保留
            province_list.append(province)
            company_code_list.append(company_code)
            cv_code_list.append(cv_code)
            cv_type_list.append(cv_type)
            sale_qty_list.append(sale_qty)
            farm_list.append(farm)
            farm_type_list.append(farm_type)
            capacity_list.append(capacity)
            capacity_crm_list.append(capacity_crm)
            capacity_piglet_list.append(capacity_piglet)
            capacity_piglet_crm_list.append(capacity_piglet_crm)

    # 导出excel
    excel_data = {}
    excel_data['省'] = province_list
    excel_data['公司'] = company_code_list
    excel_data['cv_code'] = cv_code_list
    excel_data['客户类型'] = cv_type_list
    excel_data['10月销量'] = sale_qty_list
    excel_data['种猪规模crm'] = capacity_crm_list
    excel_data['肥猪规模crm'] = capacity_piglet_crm_list
    excel_data['农场代码'] = farm_list
    excel_data['种猪规模'] = capacity_list
    excel_data['肥猪规模'] = capacity_piglet_list
    excel_data['上线版本'] = farm_type_list

    df = pd.DataFrame(excel_data)
    path = "C:/Users/yangjianzhang/Desktop/"
    df.to_excel(path + '数据_{}.xlsx'.format(datetime.datetime.now().strftime('%Y%m%d%H%M%S')), index=False)
